<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_fees Extends MY_Model
{
	private $__table ='fees';
	private $CI_instance;
	public function __construct()
	{
		parent::__construct();
		$this->CI_instance =& get_instance();
	}
	
	public function get_all_fees($id ='')
	{
		if($id == '')
		{
			$query = $this->db->where('is_misc_fee',1)->get($this->__table);
		}else{
			$query = $this->db->select(array('fee_id','fee_desc','fee_stat','fee_name','fee_rate','grade_level'))
								->where('fee_id',$id)->get($this->__table);
		}
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_tuition_fees_by_grade_level($id)
	{
		$sql = 'SELECT fee_id,fee_desc,fee_stat,fee_name,fee_rate,levels.level_desc FROM fees
				JOIN levels  ON fees.grade_level = levels.level_id
				WHERE is_tuition_fee != 0
				AND levels.level_id = ?
				';
        $query = $this->db->query($sql,array($id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function fetch_fees($type,$limit,$start)
	{
		
		$limit = intval($limit);
		$start = intval($start);
		
		$sql = 'SELECT f.fee_id,f.fee_desc,f.fee_stat,f.fee_name,f.fee_rate,l.level_desc
				FROM fees f
				LEFT JOIN levels l on l.level_id = f.grade_level
				 ';
		if($type == 'misc')
		{
			$sql .= ' where is_misc_fee = 1';
		}elseif($type == 'elec'){
			$sql .= ' where is_elective_fee = 1';
		}elseif($type == 'cca'){
			$sql .= ' where is_cca_fee = 1';
		}elseif($type == 'other'){
			$sql .= ' where is_other_fee = 1';
		}elseif($type == 'tuit'){
			$sql .= ' where is_tuition_fee = 1';
		}
		
		$sql .=' ORDER BY f.grade_level ASC LIMIT '.$start.','.$limit.';';
        $query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_all_fees($data = FALSE)
	{
		if($data == FALSE)
		{
			
			$sql = 'select count(fee_id) as total from fees where is_tuition_fee = 1';
			$query = $this->db->query($sql);
			return $query->row()->total;
		}else{
			$sql = 'select count(fee_id) as total from fees ';
			
		if($data == 'misc')
		{
			$sql .= ' where is_misc_fee = 1';
		}elseif($data == 'elec'){
			$sql .= ' where is_elective_fee = 1';
		}elseif($data == 'cca'){
			$sql .= ' where is_cca_fee = 1';
		}elseif($data == 'other'){
			$sql .= ' where is_other_fee = 1';
		}elseif($data == 'tuit'){
			$sql .= ' where is_tuition_fee = 1';
		}
			
			$query = $this->db->query($sql);
			return $query->row()->total;
		}
	}
	
	public function add_fees($data)
	{
	
		// pd($data);
		if($this->verify_data($data,2) == FALSE)
		{
			$data['fee_created'] = NOW;
			$this->db->insert($this->__table,$data);
			return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
			
		}else{
			return array('status'=>'exist');
		}
		
	}
	
	public function update_fees($data,$id)
	{
		if($this->verify_data($data,2) == FALSE)
		{
			$data['fee_updated'] = NOW;
			$this->db->set($data)->where('fee_id',$id)->update($this->__table);
			return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');
		}else
		{
			return array('status' => 'exist');
		}
	}
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level)
	{		
			if($strict_level == 1)
			{
				$query = $this->db->or_where($data)->get($this->__table);
			}elseif($strict_level == 2){
				$query = $this->db->where($data)->get($this->__table);
			}
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_fee_array($data = FALSE)
	{
		if($data !== FALSE)
		{
			$query = "SELECT fee_id,
							 concat(fee_name,' | PHP',fee_rate) as description 
					  FROM fees f
					  WHERE fee_stat = 'active' ";
			switch($data)
			{
				case  $data == 'cca':
					$query .= " AND is_cca_fee = 1";
				break;
				case  $data == 'misc':
					$query .= " AND is_misc_fee = 1";
				break;
				
				case  $data == 'tuition':
					$query .= " AND is_tuition_fee = 1";
				break;
				
				case  $data == 'others':
					$query .= " AND is_other_fee = 1";
				break;
			
				case  $data == 'electives':
					$query .= " AND is_elective_fee = 1";
				break;
			}
			
			$result = $this->db->query($query);
			
			if($result->num_rows() >=1)
			{
				$data = $result->result();
				$level[''] = '-- select uniform --';
				
				foreach($data as $k => $v)
				{
					$level[$v->fee_id] = $v->description;
				}
			
				return $level;
			}
		}else{
				return array(''=>'No Data');
		}
	}
	
	
	public function enable_misc_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_misc_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_misc_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update misc fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}	
	}
	
	public function enable_tuit_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_tuition_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_tuition_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update tuit fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}		
	}
	
	public function enable_cca_fee($x)
	{
		$fee_details = $this->db->select()
								->where('fee_id',$x)
								->get('fees');
		if($fee_details->num_rows() >= 1)
		{
			$fee = $fee_details->row();
			
			$num_of_active_fees = $this->db->select('count(fee_id) as total_active')
										   ->where('is_cca_fee',1)
										   ->where('fee_stat','active')
										   ->where('grade_level',$fee->grade_level)
										   ->get('fees');
		
			$noaf = $num_of_active_fees->num_rows() >= 1 ? $num_of_active_fees->row()->total_active : 0;

			if($noaf >= 1)
			{
				$update['fee_stat'] = 'inactive';
				$this->db->set($update)
						 ->where('is_cca_fee',1)
						 ->where('grade_level',$fee->grade_level)
						 ->update('fees');
						 
				if($this->db->affected_rows() >= 1)
				{
					$update['fee_stat'] = 'active';
					$this->db->set($update)
							 ->where('fee_id',$x)
							 ->update('fees');
				}else{
					log_message('error','Unable to update cca fees to inactive.');
					return FALSE;
				}				
			}else{
				$update['fee_stat'] = 'active';
				$this->db->set($update)->where('fee_id',$x)->update('fees');
			}
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			log_message('error','Fee details was not found. Fee Id '.$x.'');
			return FALSE;
		}		
	}
	
	public function payment_modes($id =FALSE,$type=FALSE)
	{		
	
		if($type !== FALSE)
		{
			$this->db->where('payment_mode_name',$type);
			$this->db->where('setting_value !=',0.00);
		}
		
		$query = $this->db->where('level_id',$id)
						  ->get('payment_modes');

		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	
	public function update_payment_modes($x,$type = FALSE,$levels)
	{
		if($type !== FALSE AND in_array($type,array_keys($levels)))
		{
			
			$count = 0;
			foreach($x as $k=>$v)
			{
				$sql = "UPDATE payment_modes 
					SET setting_value = ?
					WHERE setting_group = '$type'
					AND setting_name = ? ";
		
				$this->db->query($sql,array($v,$k));
				$query_made['queries'][$count] = $this->db->last_query();
				$query_made['result'][$count] = $this->db->affected_rows() >= 1 ? TRUE : NULL;
				$count++;
			}
			return in_array(1,array_values($query_made['result'])) ? TRUE : FALSE;
		}else{
			// vd($type);
			log_message('e','Update payment modes either $type is FALSE or $type not in_array');
			return FALSE;
		}
	}
	
	public function set_student_payment_mode($wazzup_id,$option)
	{
		//check to make sure if payment mode is not set
		$check = 'SELECT e.payment_mode,e.e_grade_level,l.payment_group
					FROM enrollments e
					LEFT JOIN levels l ON l.level_id = e.e_grade_level
					WHERE e_id = ?
					AND verified = 1
					AND e_archived = 0
					AND e_ay_id = ?';
					
		$q_check = $this->db->query($check,array($wazzup_id,$this->c_sy->id));
		
		if($q_check->num_rows() >=1)
		{
			$result = $q_check->row();

			// pd($result);
			//if result is null then student has no payment record set
			if($result->payment_mode == '' OR $result->payment_mode == 'none' )
			{
				if($option == 'none')
				{
					return (object)array('stat'=>false,'log'=>'Nothing to Delete.');
				}elseif($option == 'employee')
				{

					$result = $this->set_employee_payment_plan($wazzup_id,$option);
					return $result == TRUE ? (object)['stat'=>TRUE,'log'=>'Student Set to employee payment mode'] : (object)['stat'=>FALSE];

				}else{
					$get = 'INSERT INTO student_payment_modes (month_name,spm_name,spm_value,spm_category,spm_ay_id,spm_student_id)
							SELECT month_name,setting_name,setting_value,payment_mode_name,'.$this->c_sy->id.','.$wazzup_id.'
							FROM payment_modes 
							WHERE level_id =?
							AND payment_mode_name = ?';
							
					$res = $this->db->query($get,array($result->payment_group,$option));
				
					if($this->db->affected_rows() >= 1)
					{
						$this->db->set('payment_mode',$option)
								 ->where('e_id',$wazzup_id)
								 ->update('enrollments');
								 
						if($this->db->affected_rows() >= 1)
						{
							return (object)array('stat'=>true,'log'=>'Payment method was successfully set');
						}else{
							log_message('error','unable to update student payment mode in enrollment table to '.$option.'');
							return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
						}
					}else{
						// pd($this->db->last_query());
						log_message('error','unable to delete payment modes where e_id = '.$wazzup_id.' and payment option = '.$option.'');
						return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
					}
				}
			}else{
				/*
					Student already has payment mode set, 
					lets calculate to add to the selected payment mode
				*/
				$delete = 'DELETE from student_payment_modes
							WHERE spm_student_id = ?';
				$delete_q = $this->db->query($delete,array($wazzup_id));
				

				if($option == 'none')
				{
					$this->db->set('payment_mode',$option)
							 ->where('e_id',$wazzup_id)
							 ->update('enrollments');
							 
					if($this->db->affected_rows() >= 1)
					{
						return (object)array('stat'=>true,'log'=>'Payment method was successfully set');
					}else{
						log_message('error','unable to update student payment mode in enrollment table to '.$option.'');
						return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
					}
				}elseif($option == 'employee')
				{
					//employee
					// no payment has been made so

					/*
						tuition 2000
						MISC 0
						CCA as is per level
					*/
					$result = $this->set_employee_payment_plan($wazzup_id,$option);
					return $result == TRUE ? (object)['stat'=>TRUE,'log'=>'Student Set to employee payment mode'] : (object)['stat'=>FALSE];
				}else{

					$get = 'INSERT INTO student_payment_modes (month_name,spm_name,spm_value,spm_category,spm_ay_id,spm_student_id)
							SELECT month_name,setting_name,setting_value,payment_mode_name,'.$this->c_sy->id.','.$wazzup_id.'
							FROM payment_modes 
							WHERE level_id =?
							AND payment_mode_name = ?';
							
					$res = $this->db->query($get,array($result->payment_group,$option));
				}
				
				if($this->db->affected_rows() >= 1)
				{
					$this->db->set('payment_mode',$option)
							 ->where('e_id',$wazzup_id)
							 ->update('enrollments');
							 
					if($this->db->affected_rows() >= 1)
					{
						return (object)array('stat'=>true,'log'=>'Payment method was successfully set');
					}else{
						log_message('error','unable to update student payment mode in enrollment table to '.$option.'');
						return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
					}
				}else{
					log_message('error','unable to select,insert payment modes where e_id = '.$wazzup_id.' and payment option = '.$option.'');
					return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
				}
			}
		}else{
			log_message('error','NO results returned while looking for payment_mode on enrollment'.$option.'');
			return (object)array('stat'=>false,'log'=>'Unable to Set Student payment mode to student');
		}
	}
	
	public function get_total_fees_for_level($level)
	{
		//select total other fees
		$get_total_othr_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_other
								FROM fees
								WHERE fee_stat ="active" 
								AND is_other_fee = 1
								AND fee_id IN (SELECT lf_fee_id FROM level_fees WHERE lf_level_id = ?)';
		$result_other = $this->db->query($get_total_othr_sql,array($level));
		
		//select total misc fees
		$get_total_misc_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_misc 
								FROM fees
								WHERE fee_stat ="active" 
								AND is_misc_fee = 1
								AND grade_level = ?';
		$result_misc = $this->db->query($get_total_misc_sql,array($level));
								
		
		
		//select total tuitfees
		$get_total_tuit_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_tuit
								FROM fees
								WHERE fee_stat ="active" 
								AND is_tuition_fee = 1
								AND grade_level = ?';
		$result_tuit = $this->db->query($get_total_tuit_sql,array($level));
		
		//select total ccafees
		$get_total_tuit_sql = 'SELECT COALESCE(sum(fee_rate),0) AS total_cca
								FROM fees
								WHERE fee_stat ="active" 
								AND is_cca_fee = 1
								AND grade_level = ?';
		$result_cca = $this->db->query($get_total_tuit_sql,array($level));
								
		
		return $result_tuit->row()->total_tuit + $result_misc->row()->total_misc + $result_other->row()->total_other + $result_cca->row()->total_cca;
	}
	
	
	public function get_fees($type)
	{
		$sql = 'SELECT f.fee_id,f.fee_desc,f.fee_stat,f.fee_name,f.fee_rate,l.level_desc
				FROM fees f
				LEFT JOIN levels l on l.level_id = f.grade_level
				 ';
		if($type == 'misc')
		{
			$sql .= ' where is_misc_fee = 1';
		}elseif($type == 'elec'){
			$sql .= ' where is_elective_fee = 1';
		}elseif($type == 'cca'){
			$sql .= ' where is_cca_fee = 1';
		}elseif($type == 'other'){
			$sql .= ' where is_other_fee = 1';
		}elseif($type == 'tuit'){
			$sql .= ' where is_tuition_fee = 1';
		}
		
		$sql .=' ORDER BY f.fee_name,f.grade_level ASC';
		
        $query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_student_fees_by_type($type,$id)
	{
		$sql = 'SELECT sef.sef_id,sef.sef_fee_rate,sef.sef_fee_name
				FROM student_enrollment_fees sef
				WHERE sef_enrollment_id = ?
				AND sef_schoolyear_id = ?
				 ';
		if($type == 'misc')
		{
			$sql .= ' AND is_misc_fee = 1';
		}elseif($type == 'elec'){
			$sql .= ' AND is_elective_fee = 1';
		}elseif($type == 'cca'){
			$sql .= ' AND is_cca_fee = 1';
		}elseif($type == 'other'){
			$sql .= ' AND is_other_fee = 1';
		}elseif($type == 'tuit'){
			$sql .= ' AND is_tuition_fee = 1';
		}
		
		$sql.= ' LIMIT 1';
		
        $query = $this->db->query($sql,array($id,$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	public function get_student_current_payment_mode_and_due($student_id_no)
	{
		$sql = 'SELECT spm.*,e.e_id as student_enrollment_id
				FROM enrollments e
				LEFT JOIN student_payment_modes spm ON spm.spm_student_id = e.e_id
				WHERE e.verified = 1
				AND e.payment_mode != "none"
				AND e.e_archived = 0
				AND spm.spm_value != 0.00
				AND e.e_student_id = ?
				ORDER BY field(month_name,"December","January","February","March","April","May","June","July","August","September","October","November")';
				
		$res = $this->db->query($sql,array($student_id_no));

		if($res->num_rows() >= 1)
		{
			$d = $res->result();
			foreach($d as $k => $v)
			{	
				$new_pm[$k]['spm_id'] = $v->spm_id;
				$new_pm[$k]['spm_name'] =$v->spm_name;
				$new_pm[$k]['spm_value'] =$v->spm_value;
				$new_pm[$k]['spm_category'] =$v->spm_category;
				$new_pm[$k]['spm_ay_id'] = $v->spm_ay_id;
				$new_pm[$k]['spm_student_id'] = $v->spm_student_id;
				$new_pm[$k]['month_name'] = $v->month_name;
				$new_pm[$k]['student_paid'] = $this->_get_month_payment($v->student_enrollment_id,$v->month_name);
			}
			
			$this->load->helper('misc');
			return arrayToObject($new_pm);
		}
		
		
	}
	
	private function _get_month_payment($e_id,$month)
	{
		$CI =& get_instance();
		$CI->load->model('M_enrollees','meee');
		$_plan = $CI->meee->get_student_payment_mode($e_id);
	
		$sql1 = 'SELECT sum(spr.spr_ammt_paid) as monthly_sum
				FROM student_payment_records spr
				WHERE spr_schoolyear_id = ?
				AND spr_enrollment_id = ?
				AND spr_for_the_month_of = ?
				AND spr_is_deleted = 0
				AND payment_plan_type = ?';
				
				
		$sql2 = 'SELECT sum(spr.spr_ammt_paid) as monthly_sum
				FROM student_payment_records spr
				WHERE spr_schoolyear_id = ?
				AND spr_enrollment_id = ?
				AND spr_for_the_month_of = ?
				AND spr_is_deleted = 0';
		$q = $this->db->query($sql1,array($this->c_sy->id,$e_id,$month,$_plan));
		
		if($q->num_rows() >= 1)
		{
			if($q->row()->monthly_sum == NULL){
				return FALSE;
			}else{
				return $q->row()->monthly_sum;
			}
			
		}else{
			// pd('there');
			$q2 = $this->db->query($sql2,array($this->c_sy->id,$e_id,$month));
			return $q2->num_rows() >=1 ? $q2->row()->monthly_sum : FALSE;
		}
	}
	
	public function get_foreign_fees()
	{
		$ci =& get_instance();
		$ci->load->model('M_levels','ml');
		
		$levels = $ci->ml->get_all_levels();
		
		
		foreach($levels as $k => $v)
		{
			$data[$k]['fees'] = $this->_get_level_foreign_fees($v->level_id);
			$data[$k]['level_desc'] = $v->level_desc;
			$data[$k]['level_code'] = $v->level_code;
			$data[$k]['level_id'] = $v->level_id;
		}
		
		return $data;
	}
	public function get_op_fees()
	{
		$sq = $this->db->where('is_elective_fee',1)
					   ->where('fee_stat','active')
					   ->get('fees');
					   
					   
		if($sq->num_rows() >=1)
		{
			$data[''] = 'Select Foreign Fees';
			foreach($sq->result() as $k => $v)
			{
				$data[$v->fee_id] = $v->fee_name .' | '.$v->fee_rate;
			}
			
			return $data;
		}else{
		
			return array(''=>'No Optional or Additional Fees Found');
		}
	}
	
	private function _get_level_foreign_fees($id)
	{
		$q = $this->db->where('level_id',$id)->get('foreign_fee');
		return $q-> num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function set_foreign_fees($post)
	{
		$d = (object)$post;
		$sql  = 'INSERT INTO foreign_fee(fee_name,fee_rate,fee_desc,level_id,gp_id,sy_id,created)
				 SELECT fee_name,fee_rate,fee_desc,?,?,?,? FROM fees
				 WHERE fees.fee_id = ?';
				 
		$this->db->query($sql,array($d->level,$this->c_gp->gp_id,$this->c_sy->id,NOW,$d->fees));
		
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_payment_mode_by_category($cat)
	{
		$ci =& get_instance();
		
		$ci->load->model('M_levels','ml');
		$levels = $ci->ml->get_all_levels();
		
		
		// pd($levels);
		if($levels)
		{
			foreach($levels as $k => $v)
			{
				$data[$k]['level_name'] = $v->level_code;
				$data[$k]['level_id'] = $v->level_desc;
				$data[$k]['students'] = $this->_get_students_by_payment_mode_and_grade_level($v->level_id,$cat);
			
			}
			
			return $data;
		}
	}
	
	public function count_students_with_payment_plan_of($pm = '')
	{
		$sql = 'SELECT count(e_id) as total 
			  FROM enrollments 
			  WHERE payment_mode = ? 
			  AND e_archived = 0';
			  
		$q = $this->db->query($sql,array($pm));
		return $q->num_rows() >= 1 ? $q->row()->total : 0;
	
	}
	
	private function _get_students_by_payment_mode_and_grade_level($level,$cat)
	{
		$q = $this->db->select('e_id,
						   concat(p.firstname," ",p.middlename," ",p.lastname) as fullname,
						   gender',FALSE)
				 ->join('profiles p','e.e_profile_id = p.profile_id','left')
				 ->where('e.e_grade_level',$level)
				 ->where('e.payment_mode',$cat)
				 ->where('e.e_archived',0)
				 ->where('e.verified',1)
				 ->where('e.e_ay_id',$this->c_sy->id)
				 ->get('enrollments e');
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	
	public function get_student_current_payment_mode_and_due_by_enrollment_id($e_id,$type = FALSE)
	{
	
		$sql = 'SELECT spm.*
				FROM enrollments e
				LEFT JOIN student_payment_modes spm ON spm.spm_student_id = e.e_id AND spm.spm_category = e.payment_mode
				WHERE e.verified = 1
				AND e.payment_mode != "none"
				AND e.e_archived = 0
				AND spm.spm_value != 0.00
				AND e.e_id = ? ';
		
		if(!in_array(strtolower($type),['employee']))
		{
			$sql .= ' ORDER BY field(month_name,"January Registration","April","May","June","July","August","September","October","November","December","January","February","March")';
		}

		$res = $this->db->query($sql,array($e_id));
		// pd($this->db->last_query());
		if($res->num_rows() >= 1)
		{
			$d = $res->result();
			foreach($d as $k => $v)
			{	
				$new_pm[$k]['spm_id'] = $v->spm_id;
				$new_pm[$k]['spm_name'] =$v->spm_name;
				$new_pm[$k]['spm_value'] =$v->spm_value;
				$new_pm[$k]['spm_category'] =$v->spm_category;
				$new_pm[$k]['spm_ay_id'] = $v->spm_ay_id;
				$new_pm[$k]['spm_student_id'] = $v->spm_student_id;
				$new_pm[$k]['month_name'] = $v->month_name;
				$new_pm[$k]['student_paid'] = $v->student_paid;
			}
			
			$this->load->helper('misc');
			return arrayToObject($new_pm);
		}
	}
	
	public function delete_foreign_fee_from_level_list($x)
	{
		$this->db->where('id',$x)
				 ->where('sy_id',$this->c_sy->id)
				 ->limit(1)
				 ->delete('foreign_fee');
				 
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_other_fees()
	{
		$q = $this->db->select('fee_id,fee_name,fee_rate')
					  ->where('is_other_fee',1)
					  ->get('fees');
		
		if($q->num_rows() >= 1)
		{
			$data[''] = '-- Select Items --';
			foreach($q->result() as $k => $v)
			{
				$data[$v->fee_id.'_'.$v->fee_rate] = ucwords($v->fee_name).' / Php '.number_format($v->fee_rate,2,'.',',');
			}
		}
		
		return $q->num_rows() >= 1 ? $data : FALSE;
	}
	
	public function get_other_fees_where($x)
	{
		$q = $this->db->select('fee_id,
								concat(fee_name," | ",fee_rate) as label ,
								fee_name as value ,
								fee_rate as id
								',FALSE)
					  ->where('is_other_fee',1)
					  ->like('fee_name',$x)
					  ->get('fees');
		
		return $q->num_rows() >= 1 ? $q->result_array() : FALSE;
		// return $this->db->last_query();
	}
	
	public function recompute_student_fees($e_id)
	{
		// get assigned payment group
		$payment_group = $this->db->select('payment_group')
								->join('levels','levels.level_id=enrollments.e_grade_level')
								->where('e_id',$e_id)->limit(1)
								->get('enrollments')
								->row()
								->payment_group;

		// pd($payment_group);
		//select group fees assigned for the payment group
		$feesql = "SELECT *,
					IF(fee_type = 'tuit',1,0) as is_tuition_fee,
					IF(fee_type = 'misc',1,0) as is_misc_fee,
					IF(fee_type = 'cca',1,0) as is_cca_fee
				FROM (`payment_group_fees`)
				WHERE `group_id` = ?";
		$feesres = $this->db->query($feesql,array($payment_group));
		// pd($feesres->result());
		
		$this->fees = $feesres->num_rows() >= 1 ? $feesres->result() : NULL;
		
		//current payment record id, if student already paid
		$this->CI_instance->load->model('M_student_payment_record');
		$c_spr_id = $this->CI_instance->M_student_payment_record->current_payment_record_id($e_id);
		
		// pd($c_spr_id);
		//prep the fees to be inserted
		foreach($this->fees as $key => $v)
		{
			$data[$key]['sef_fee_name'] = $v->fee_name;
			$data[$key]['sef_fee_rate'] = $v->fee_price;
			$data[$key]['sef_enrollment_id'] = $e_id;
			$data[$key]['sef_gperiod_id'] = $this->c_gp->gp_id;
			$data[$key]['sef_schoolyear_id'] = $this->c_sy->id;
			$data[$key]['sef_created'] = NOW;
			$data[$key]['is_tuition_fee'] = $v->is_tuition_fee;
			$data[$key]['is_misc_fee'] = $v->is_misc_fee;
			$data[$key]['is_cca_fee'] = $v->is_cca_fee;
		}
		


		//delete student enrollment fees because we will insert a new one.
		// this is better than updating the old fees, much faster
		$this->db->where('sef_enrollment_id',$e_id)
				 ->where('sef_schoolyear_id',$this->c_sy->id)
				 ->where('is_elective_fee !=', 1)
				 ->delete('student_enrollment_fees');
		
		
		foreach($data as $k => $nd)
		{
			$this->db->insert('student_enrollment_fees',$nd);
			
			if($nd['is_tuition_fee'])
			{
				$insert_keys_from_sef_table['tuit'] = $this->db->insert_id();
			}elseif($nd['is_misc_fee'])
			{
				$insert_keys_from_sef_table['misc'] = $this->db->insert_id();
			}elseif($nd['is_cca_fee'])
			{
				$insert_keys_from_sef_table['cca'] = $this->db->insert_id();
			}else{
				
			}
		}
		

		// pd($insert_keys_from_sef_table);
		if($c_spr_id !== NULL)
		{
			$breakdwon = $this->db->select('spb_id')->where('spb_spr_id',$c_spr_id)->get('student_payment_breakdown');
			// pd($breakdwon);
			if($breakdwon->num_rows() >= 1 )
			{
				//for spb_sef_id
				$misc_new_sef_id = $insert_keys_from_sef_table['misc'];
				$tuit_new_sef_id = $insert_keys_from_sef_table['tuit'];
				$cca_new_sef_id = $insert_keys_from_sef_table['cca'];
				
				//raw data for future
				$new_misc_raw = $this->get_student_fees_by_type('misc',$e_id);
				$new_tuit_raw = $this->get_student_fees_by_type('tuit',$e_id);
				$new_cca_raw  = $this->get_student_fees_by_type('cca',$e_id);
				// pp($new_misc_raw);
				// pp($new_tuit_raw);
				// pp($new_cca_raw);

				// prep data
				$new_misc = $new_misc_raw->sef_fee_rate;
				$new_tuit = $new_tuit_raw->sef_fee_rate;
				$new_cca = $new_cca_raw->sef_fee_rate;
				$new_misc_sef_id = $new_misc_raw->sef_id;
				$new_tuit_sef_id = $new_tuit_raw->sef_id;
				$new_cca_sef_id = $new_cca_raw->sef_id;
				// vd($new_misc.' '.$new_tuit.' '.$new_cca);

				// raw queries
				$apply_misc = "UPDATE student_payment_breakdown
							   SET spb_fee_rate = ?, 
								   spb_remaining_balance = {$new_misc} - spb_amount_paid,
								   spb_sef_id = ?
							   WHERE spb_spr_id = ?
							   AND spb_is_misc_fee = 1";
				
				$apply_tuit = "UPDATE student_payment_breakdown
							   SET spb_fee_rate = ?, 
								   spb_remaining_balance = {$new_tuit} - spb_amount_paid,
								   spb_sef_id = ?
							   WHERE spb_spr_id = ?
							   AND spb_is_tuition_fee = 1";
							   
				$apply_cca = "UPDATE student_payment_breakdown
							   SET spb_fee_rate = ?, 
								   spb_remaining_balance = {$new_cca} - spb_amount_paid,
								   spb_sef_id = ?
							   WHERE spb_spr_id = ?
							   AND spb_is_cca_fee = 1";
							   
				$misc_query = $this->db->query($apply_misc,array($new_misc,$misc_new_sef_id,$c_spr_id));
				// pd($this->db->last_query());
				$flag['misc_query'] = $this->db->affected_rows() >=1 ? 1 : 0;
				
				$tuit_query = $this->db->query($apply_tuit,array($new_tuit,$tuit_new_sef_id,$c_spr_id));
				$flag['tuit_query'] = $this->db->affected_rows() >=1 ? 1 : 0;
				
				$cca_query = $this->db->query($apply_cca,array($new_cca,$cca_new_sef_id,$c_spr_id));
				$flag['cca_query'] = $this->db->affected_rows() >=1 ? 1 : 0;

				// pp($new_misc);
				// pp($new_tuit);
				// pd($flag);

				if(count(array_unique($flag)) === 1)  
				{
					$update_spb = current($flag);
				}else{
					$update_spb = FALSE;
				}
			}else{
				$updated_spb = TRUE;
			}
		}
		
		return $updated_spb = isset($update_spb) ? $update_spb : TRUE;
	}
	
	public function recompute_payment_plan($id)
	{
		//check if student is assigned to a payment plan
		$check = 'SELECT e.payment_mode,e.e_grade_level,l.payment_group
			FROM enrollments e
			LEFT JOIN levels l ON l.level_id = e.e_grade_level
			WHERE e_id = ?
			AND verified = 1
			AND e_archived = 0
			AND e_ay_id = ?';
			
		$q_check = $this->db->query($check,array($id,$this->c_sy->id));
		$stud = $q_check->num_rows() >= 1 ? $q_check->row() : FALSE;
		
		//if yes
		if($stud)
		{
			//get student payment plan
			if($stud->payment_mode == 'none' OR $stud->payment_mode == NULL)
			{
				//if student has a payment plan of none or null
				//thou shalt not proceed
				return TRUE;
			}else{

				//else we delete the old payment plan/mode
				$this->db->where('spm_student_id',$id)
						 ->where('spm_ay_id',$this->c_sy->id)
						 ->delete('student_payment_modes');
						 
						 
				if($this->db->affected_rows() >= 1)
				{
					if(in_array($stud->payment_mode,['employee']))
					{
						// if the payment plan is employee do this
						return $this->set_employee_payment_plan($id,'employee');
					}else{
						//if the payment plan is existing and it is not an employee do this
						$get = 'INSERT INTO student_payment_modes (month_name,spm_name,spm_value,spm_category,spm_ay_id,spm_student_id)
								SELECT month_name,setting_name,setting_value,payment_mode_name,'.$this->c_sy->id.','.$id.'
								FROM payment_modes 
								WHERE level_id =?
								AND payment_mode_name = ?';
						$this->db->query($get,array($stud->payment_group,$stud->payment_mode));
						return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
					}
				}else{
					// unable to delete payment plan
					// let us not continue further down the path
					return FALSE;
				}
			}
		}else{
			// the student is not assigned to a payment plan
			// thou shall not proceed
			return FALSE;
		}
	}
	
	public function get_billing_by_batch_code($batch_code,$type)
	{
		$c = $this->count_students_with_payment_plan_of($type);
	
		$sql = 'SELECT sbh.enrollment_id,
					   concat(p.firstname," ",p.lastname) as fullname,
					   e.e_student_id as student_id,
					   sbh.id as iids
				FROM student_billing_header sbh
				LEFT JOIN enrollments e ON e.e_id = sbh.enrollment_id
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				WHERE sbh.batch_code = ?
				AND sbh.sy_id = ?';
		$q = $this->db->query($sql,array($batch_code,$this->c_sy->id));
		
		if($q->num_rows() >= 1 )
		{
			foreach($q->result_array() as $k => $v)
			{
				$formatted[$k]['enrollment_id'] = $v['enrollment_id'];
				$formatted[$k]['fullname'] = $v['fullname'];
				$formatted[$k]['student_id'] = $v['student_id'];
				$formatted[$k]['view_pdf'] = $this->hs->encrypt(intval($v['enrollment_id']),intval($v['iids']));
			}
			
			// pd($formatted);
			$res['stat'] = TRUE;
			$res['value'] = $formatted;
			$res['count'] = $q->num_rows() == $c;
		}else{
			$res['stat'] = FALSE;
		}
		
		
		return (object)$res;
	}
	
	public function count_students_with_paymentmode($mode)
	{
		$sql = 'SELECT COUNT( DISTINCT e_id ) AS total_enrollments
				FROM enrollments
				WHERE payment_mode =  ?
				AND verified = 1
				AND e_archived = 0
				AND e_ay_id = ?';
		$q = $this->db->query($sql,array($mode,$this->c_sy->id));
				
		return $q->num_rows() >= 1 ? $q->row()->total_enrollments : 0;
	}
	
	public function delete_fee($id)
	{
		$this->db->where('fee_id',$id)->limit(1)->delete('fees');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_students_by_payment_plans($plan = '')
	{
		$q = $this->db->select('e_id,
						   concat_ws("",p.lastname," , ",p.middlename," ",p.firstname) as fullname,
						   concat_ws("_",p.lastname,"",p.middlename,"",p.firstname) as slugname,
						   l.level_desc,
						   gender',FALSE)
				 ->join('profiles p','e.e_profile_id = p.profile_id','left')
				 ->join('levels l','e.e_grade_level = l.level_id','left')
				 ->where('e.payment_mode',$plan)
				 ->where('e.e_archived',0)
				 ->where('e.verified',1)
				 ->where('e.e_ay_id',$this->c_sy->id)
				 ->order_by('p.lastname')
				 ->get('enrollments e');
		return $q->num_rows() >= 1 ? $q->result() : FALSE;	
	}
	
	public function check_if_paid($id = '')
	{
		$sql = 'SELECT spr_id 
				FROM student_payment_records 
				WHERE spr_enrollment_id = ? 
				AND spr_is_deleted = 0
				AND spr_schoolyear_id = ?';
				
		$res = $this->db->query($sql,array($id,$this->c_sy->id));
		return $res->num_rows() >=1 ? TRUE : FALSE;

	}
	
	//This will get all paid fees and reapply ro student payment plan
	//
	public function recalculate_paid_fees($id,$type)
	{
		//+------------
		//get CI instance
		//because get ci instance
		$CI =& get_instance();
		//library we will use to apply all fees to the months paid
		$CI->load->library('cpp_breaker');
		//+------------

		
		// $sql =' SELECT sum(monthly_total) as total_paid
		// 		FROM student_payment_records 
		// 		WHERE spr_enrollment_id = ?
		// 		AND spr_schoolyear_id = ?
		// 		AND spr_is_deleted = 0 ';
				
		// $test = $this->db->query($sql,[$id,$this->c_sy->id]);

		// if($test->num_rows() >=1)
		// {
		// 	$q = $test;
		// }else{

		//get all total paid in student_payment_records table
		$sql =' SELECT sum(spr_ammt_paid) as total_paid
				FROM student_payment_records 
				WHERE spr_enrollment_id = ?
				AND spr_schoolyear_id = ?
				AND spr_is_deleted = 0 ';
				
		$q = $this->db->query($sql,[$id,$this->c_sy->id]);

		// }
		//if yes
		if($q->num_rows() >=1 )
		{
			//and if not lesser than zero, then student has paid
			if($q->row()->total_paid > 0)
			{
				//
				$scheme = $this->get_student_current_payment_mode_and_due_by_enrollment_id($id,$type);

				// vd($scheme);
				$this->cpp_breaker
					 ->set_month($scheme)//insert the scheme
					 ->set_amount($q->row()->total_paid);//insert the total paid
				$result = $this->cpp_breaker->init(); //start breaking the total payments to each month
				
				//if result is successfull
				if($result !== FALSE)
				{
					//use the returned data as update 
					$this->db->update_batch('student_payment_modes',$result,'spm_id');
					return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
				}else{
					return FALSE;
				}
			}else{
				//no payments made yet just return true
				return TRUE;
			}
		}else{
			return FALSE;
		}
	}

	public function assign_batch_fees_to_level($level = FALSE,$fees = FALSE)
	{

		$status = [];
		$student = 'SELECT e_id 
					FROM enrollments 
					WHERE e_grade_level = ?
					AND registrar_approved = 1
					AND verified = 1
					AND e_ay_id = ?';
		$g_students = $this->db->query($student,[$level,$this->c_sy->id]);


		// return $this->db->last_query();
		if($g_students->num_rows() >=1)
		{
			foreach($g_students->result() as $k => $v)
			{
				$status[] = $this->_assign_batch_fees_to_level($v->e_id,$fees);
			}

			if(count(array_keys($status, true)) == count($status)){
				return 'all';
			}elseif(count(array_keys($status, true)) < count($status)){
				return 'notall';
			}elseif(count(array_keys($status, true)) == 0){
				return 'noupdate';
			}
			
		}else{
			return FALSE;
		}
	}

	private function _assign_batch_fees_to_level($e_id = FALSE,$fees = FALSE)
	{
		set_time_limit(0);
		$ci =& get_instance();

		$ci->load->model(['M_enrollees','M_scholarships','M_student_payment_record']);

		$c_spr_id = $ci->M_student_payment_record->current_payment_record_id($e_id);


		$fee_ids = "'".implode("','",$fees)."'";

		// pd($fee_ids);

		if($fee_ids)
		{
			/*GET SELECTED FEES FROM DATABSE*/
			$fees = "SELECT * FROM fees WHERE fee_id IN ({$fee_ids})";
			$r_fees = $this->db->query($fees);

			if($r_fees->num_rows() >=1)
			{
				$counter =0;
				foreach($r_fees->result() as $key => $value)
				{
					$data[$counter]['sef_fee_name'] = $value->fee_name;
					$data[$counter]['sef_fee_rate'] = $value->fee_rate;
					$data[$counter]['sef_enrollment_id'] = $e_id;
					$data[$counter]['sef_gperiod_id'] = $this->c_gp->gp_id;
					$data[$counter]['sef_schoolyear_id'] = $this->c_sy->id;
					$data[$counter]['sef_created'] =NOW;
					$data[$counter]['is_elective_fee'] = 1;
						
					if($c_spr_id !== NULL)
					{
							$for_pb[$counter]['spb_fee_name'] =$value->fee_name;
							$for_pb[$counter]['spb_remaining_balance'] = $value->fee_rate;
							$for_pb[$counter]['spb_fee_rate'] =$value->fee_rate;
							$for_pb[$counter]['spb_transacted_by'] =$this->session->userdata('userid');
							$for_pb[$counter]['spb_spr_id'] =$c_spr_id;
							$for_pb[$counter]['spb_created'] = NOW;
							$for_pb[$counter]['spb_stud_enrollment_id'] = $e_id;
							$for_pb[$counter]['is_elective_fee'] = 1;
					}else{
						$for_pb = FALSE;
					}
					$counter++;
				}

				if($ci->M_enrollees->cashier_add_new_efees($data,$for_pb) == TRUE)
				{

					$res = $this->recompute_student_fees($e_id);
					$type = $ci->M_enrollees->get_student_payment_mode($e_id);
					//recompute the payment plan
					$recompute_payment_plan = $this->recompute_payment_plan($e_id);
					//recalculate paid fees
					$add_payment_to_fees = $this->recalculate_paid_fees($e_id,$type);
					// add and compute scholarships
					$ci->M_scholarships->readd_scholarships($e_id);
					// add and compute deductions
					$ci->M_scholarships->readd_deductions($e_id);

					return TRUE;
				}else{
					return FALSE;
				}
			}
		}else{
			return FALSE;
		}
	}


	//wazzup_id is e_id
	public function set_employee_payment_plan($wazzup_id,$option)
	{
		/*
			tuition 2000
			MISC 0
			CCA as is per level
		*/
		$delete_misc = 'UPDATE student_enrollment_fees 
						SET sef_fee_rate = 0 
						WHERE is_misc_fee = 1 
						AND sef_enrollment_id = ?
						AND sef_schoolyear_id = ?
						LIMIT 1';

		$change_tuit = 'UPDATE student_enrollment_fees
						SET sef_fee_rate = 2000
						WHERE is_tuition_fee = 1 
						AND sef_enrollment_id = ?
						AND sef_schoolyear_id = ?
						LIMIT 1';

		$get_student_cca = 'SELECT sef_fee_rate 
							FROM student_enrollment_fees 
							WHERE is_cca_fee = 1 
							AND sef_enrollment_id = ?
							AND sef_schoolyear_id = ?
							LIMIT 1';

		$update_enrollment = '';

		$r_delete_misc = $this->db->query($delete_misc,[$wazzup_id,$this->c_sy->id]);
		$r_change_tuit = $this->db->query($change_tuit,[$wazzup_id,$this->c_sy->id]);
		$r_get_cca = $this->db->query($get_student_cca,[$wazzup_id,$this->c_sy->id]);


		$cca = $r_get_cca->num_rows() >=1 ? $r_get_cca->row()->sef_fee_rate : 0;


		$employee['month_name'] = 'employee';
		$employee['spm_name'] = 'employee';
		$employee['spm_value'] = $cca + 2000;;
		$employee['spm_category'] = 'employee';
		$employee['spm_ay_id'] = $this->c_sy->id;
		$employee['spm_student_id'] = $wazzup_id;

		if($this->db->affected_rows()>=1)
		{
			//insert
			$this->db->insert('student_payment_modes',$employee);
			if($this->db->affected_rows()>=1)
			{
				$select = $this->db->select('payment_mode')->where('e_id',$wazzup_id)->get('enrollments');

				if($select->num_rows() >=1)
				{
					if($select->row()->payment_mode == 'employee')
					{
						return TRUE;
					}else{
						$set['payment_mode'] = 'employee';
						$this->db->set($set)
						 		 ->where('e_id',$wazzup_id)
						 		 ->update('enrollments');
						return $this->db->affected_rows() >=1 ? TRUE : FALSE;
					}
				}else{
					return FALSE;
				}
			}else{
				return 'Unable to insert payment modes';
			}
		}else{
			return 'Unable to delete old';
		}

		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}

	public function get_balance_report($lvl)
	{
		$students = $this->_gbr_get_student_levels($lvl);
		if($students)
		{
			foreach ($students as $sk => $sv) 
			{
				$stud[$sk]['fullname'] = ucwords(strtolower($sv->student_full_name));
				$stud[$sk]['grade_level'] = ucwords(strtolower($sv->grade_level));
				$stud[$sk]['gender'] = $sv->gender;
				$stud[$sk]['remaining_balance'] = $this->_gbr_get_student_balance($sv->enrollment_id,$sv->profile_id);
			}

			// pd($stud);
			return $stud;
		}else{
			return FALSE;
		}
	}

	private function _gbr_get_student_levels($lvl = 0)
	{
		//function called by get_balance_report()
		$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname) as student_full_name,
					   p.gender,
					   l.level_desc as grade_level,
					   e.e_id as enrollment_id,
					   e.e_profile_id as profile_id
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				WHERE e.registrar_approved = 1
				AND e.e_archived = 0
				AND e.e_ay_id = ? ';

		if($lvl == 0){
			$sql .=' ORDER BY p.lastname';
			$res = $this->db->query($sql,[$this->c_sy->id]);
		}else{
			$sql .=' AND e.e_grade_level = ? ORDER BY p.lastname';
			$res = $this->db->query($sql,[$this->c_sy->id,$lvl]);			
		}

		$res = $this->db->query($sql,[$this->c_sy->id,$lvl]);
		return $res->num_rows() >= 1 ? $res->result() : FALSE;
	}
	//param enrollment id and profile id
	private function _gbr_get_student_balance($e_id,$p_id)
	{
		$CI =& get_instance();
		$CI->load->model('M_enrollees');
		
		$sum_amount_due =$CI->M_enrollees->get_sum_of_total_payables($e_id,$this->c_sy->id);
		$electives_total = $CI->M_enrollees->get_sum_of_student_electives($e_id);
		$sum_paid_fees = $CI->M_enrollees->get_sum_of_paid_fees($e_id,$this->c_sy->id);

		return round(($sum_amount_due + $electives_total) - $sum_paid_fees,2);
	}
}
